package com.pactera.madp.cp.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateField;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSONArray;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.pactera.madp.common.core.util.R;
import com.pactera.madp.common.data.tenant.TenantContextHolder;
import com.pactera.madp.cp.api.dto.contact.ContactDetailsTendencyDTO;
import com.pactera.madp.cp.api.entity.*;
import com.pactera.madp.cp.api.entity.clientLossManagement.ContactLossEntity;
import com.pactera.madp.cp.api.entity.workroom.WorkContactEmployeeStatisticsEntity;
import com.pactera.madp.cp.api.feign.RemoteDeptService;
import com.pactera.madp.cp.api.model.workcontact.WorkContactLossData;
import com.pactera.madp.cp.api.model.workcontact.WorkContactNewAddData;
import com.pactera.madp.cp.api.vo.contact.ContactStaticsDetailsVO;
import com.pactera.madp.cp.api.vo.contact.ContactStaticsTendencyVO;
import com.pactera.madp.cp.api.vo.contact.ContactStatisticsDataVO;
import com.pactera.madp.cp.common.util.PageUtil;
import com.pactera.madp.cp.handler.CorpContextHolder;
import com.pactera.madp.cp.mapper.WorkContactEmployeeStaticsMapper;
import com.pactera.madp.cp.service.*;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

import static java.util.stream.Collectors.toList;

/**
 * WorkContactEmployeeStaticsServiceImpl
 *
 * @author jun
 * @version 1.0
 * 2021/7/19 17:24
 **/
@Service
@Slf4j
@AllArgsConstructor
public class WorkContactEmployeeStaticsServiceImpl extends ServiceImpl<WorkContactEmployeeStaticsMapper, WorkContactEmployeeStatisticsEntity> implements WorkContactEmployeeStaticsService {

    private final IWorkContactEmployeeService workContactEmployeeService;
    private final IWorkContactDailyService workContactDailyService;
    private final RemoteDeptService remoteDeptService;
    private final IWorkEmployeeService employeeService;
    private final IWorkDeptService deptService;
    private final IContactLossService contactLossService;
    private final IWorkContactService workContactService;
    private final WorkCustomInfoService workCustomInfoService;

    @Override
//    @Async
    public void synchronizationContact(Integer corpId) {
        DateTime yesterday = DateUtil.beginOfDay(DateUtil.yesterday());

        List<WorkContactEmployeeStatisticsEntity> list = list(Wrappers.<WorkContactEmployeeStatisticsEntity>lambdaQuery()
                .eq(WorkContactEmployeeStatisticsEntity::getCorpId, corpId)
                .eq(BaseEntity::getCreateTime, yesterday));

        //获取客户总数
        QueryWrapper<WorkContactEmployeeEntity> totalContactsWrapper = new QueryWrapper<>();
        totalContactsWrapper.select("employee_id, COUNT(employee_id) as total_contact");
        totalContactsWrapper.eq("corp_id", corpId);
        totalContactsWrapper.eq("tenant_id", TenantContextHolder.getTenantId());
        totalContactsWrapper.le("create_time", DateUtil.endOfDay(DateUtil.yesterday()));
        totalContactsWrapper.groupBy("employee_id");
        List<Map<String, Object>> totalContactList = workContactEmployeeService.listMaps(totalContactsWrapper);
        List<Map<String, Object>> distinctContactList = workContactEmployeeService.getDistinctContactlistMaps(corpId, TenantContextHolder.getTenantId(), DateUtil.endOfDay(DateUtil.yesterday()));
        log.info("distinctContactList:{}",distinctContactList);
        List<WorkContactEmployeeStatisticsEntity> entities = new LinkedList<>();
        if (list.isEmpty()) {
            totalContactList.forEach(l -> {
                WorkContactEmployeeStatisticsEntity entity = new WorkContactEmployeeStatisticsEntity();
                entity.setDistinctContact(0);
                distinctContactList.forEach(d->{
                    if (l.get("employee_id").toString().equals(d.get("employee_id").toString())) {
                        entity.setDistinctContact(Integer.valueOf(d.get("distinct_contact").toString()));
                        return;
                    }
                });
                entity.setEmployeeId(Integer.valueOf(l.get("employee_id").toString()));
                entity.setTotalContact(Integer.valueOf(l.get("total_contact").toString()));
                entity.setLossContact(0);
                entity.setNewAddContact(0);
                entity.setRealAddContact(0);
                entity.setCrmContact(getCrmContact(Integer.valueOf(l.get("employee_id").toString())));
                entity.setNonBankContact((entity.getDistinctContact() - entity.getCrmContact()) < 0 ? 0 : entity.getDistinctContact() - entity.getCrmContact());
                entity.setCorpId(corpId);
                entity.setCreateTime(DateUtil.toLocalDateTime(yesterday));
                entity.setTenantId(TenantContextHolder.getTenantId());
                entities.add(entity);
            });
        } else {
            list.forEach(l -> {
                for (int i = 0; i < totalContactList.size(); i++) {
                    if (l.getEmployeeId().equals(Integer.valueOf(totalContactList.get(i).get("employee_id").toString()))){
                        l.setEmployeeId(Integer.valueOf(totalContactList.get(i).get("employee_id").toString()));
                        l.setDistinctContact(0);
                        distinctContactList.forEach(d->{
                            if (l.getEmployeeId().equals(Integer.valueOf(d.get("employee_id").toString()))) {
                                l.setDistinctContact(Integer.valueOf(d.get("distinct_contact").toString()));
                                return;
                            }
                        });
                        l.setTotalContact(Integer.valueOf(totalContactList.get(i).get("total_contact").toString()));
                        l.setLossContact(0);
                        l.setNewAddContact(0);
                        l.setRealAddContact(0);
                        l.setCrmContact(getCrmContact(Integer.valueOf(totalContactList.get(i).get("employee_id").toString())));
                        l.setNonBankContact((l.getDistinctContact() - l.getCrmContact()) < 0 ? 0 : l.getDistinctContact() - l.getCrmContact());
                        l.setCorpId(corpId);
                        l.setCreateTime(DateUtil.toLocalDateTime(yesterday));
                        l.setTenantId(TenantContextHolder.getTenantId());
                        entities.add(l);
                    }
                }

            });
        }

        List<WorkContactNewAddData> addContact = this.baseMapper.getNewAddContact(DateUtil.beginOfDay(yesterday).toDateStr() + " 00:00:00", DateUtil.endOfDay(yesterday).toDateStr() + " 23:59:59", corpId);

        entities.forEach(e->{
            addContact.forEach(lo ->{
                if (lo.getEmployeeId().equals(e.getEmployeeId())){
                    e.setNewAddContact(lo.getNewAddContact());
                }
            });
        });

        //获取净增客户数
        QueryWrapper<WorkContactEmployeeEntity> realAddContact = new QueryWrapper<>();
        realAddContact.select("employee_id,COUNT(employee_id) as real_add_contact");
        realAddContact.notIn("contact_id","select contact_id FROM cp_work_contact_employee WHERE del_flag = 1");
        realAddContact.eq("corp_id",corpId);
        realAddContact.eq("tenant_id",TenantContextHolder.getTenantId());
        realAddContact.ge("create_time",DateUtil.beginOfDay(yesterday));
        realAddContact.le("create_time",DateUtil.endOfDay(yesterday));
        realAddContact.groupBy("employee_id");

        List<Map<String, Object>> realAddContactList = workContactEmployeeService.listMaps(realAddContact);
        entities.forEach(e->{
            realAddContactList.forEach(r ->{
                Integer employeeId = Integer.valueOf(r.get("employee_id").toString());
                if (employeeId.equals(e.getEmployeeId())){
                    e.setRealAddContact(Integer.valueOf(r.get("real_add_contact").toString()));
                }
            });
        });

        List<WorkContactLossData> lossContact = baseMapper.getLossContact(DateUtil.beginOfDay(yesterday).toDateStr()+" 00:00:00", DateUtil.endOfDay(yesterday).toDateStr()+" 23:59:59",corpId);

        entities.forEach(e->{
            lossContact.forEach(lo ->{
                if (lo.getEmployeeId().equals(e.getEmployeeId())){
                    e.setLossContact(lo.getLossContact());
                }
            });
        });
        if (!entities.isEmpty()){
            saveOrUpdateBatch(entities);
        }
    }

    private Integer getCrmContact(Integer employeeId) {
        WorkEmployeeEntity employee = employeeService.getById(employeeId);
        ArrayList<String> wxUserIds = new ArrayList<>();
        wxUserIds.add(employee.getWxUserId());
        List<String> contactMobiles = workContactEmployeeService.list(Wrappers.<WorkContactEmployeeEntity>lambdaQuery()
                .eq(WorkContactEmployeeEntity::getCorpId, CorpContextHolder.getCorpId())
                .in(WorkContactEmployeeEntity::getEmployeeId, employeeId))
                .stream()
                .map(l -> {
                    l.setRemarkMobiles(JSONArray.parseArray(l.getRemarkMobiles()).isEmpty() ? "" : JSONArray.parseArray(l.getRemarkMobiles()).get(0).toString());
                    return l;
                })
                .filter(l -> !l.getRemarkMobiles().equals(""))
                .map(WorkContactEmployeeEntity::getRemarkMobiles)
                .distinct()
                .collect(Collectors.toList());
        //通过手机号匹配,获取CRM客户数
        return contactMobiles.isEmpty() ? 0 : workCustomInfoService.countInCustMgrNoAndMobile(wxUserIds,contactMobiles);
    }

    @Override
    public ContactStaticsDetailsVO getContactDetails() {
        DateTimeFormatter pattern = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        String format = pattern.format(LocalDateTime.now().plusDays(-1));

        QueryWrapper<WorkContactEmployeeStatisticsEntity> wrapper = new QueryWrapper<>();
        wrapper.select("sum(total_contact) as  totalContact,sum(real_add_contact) as realAddContact,sum(new_add_contact) as newAddContact,sum(loss_contact) as lossContact");
        wrapper.ge("create_time",format+" 00:00:00");
        wrapper.le("create_time",format+" 23:59:59");
        wrapper.eq("corp_id",CorpContextHolder.getCorpId());
        Map<String, Object> map = getMap(wrapper);


        ContactStaticsDetailsVO detailsVO = new ContactStaticsDetailsVO();
        detailsVO.setTotalContact(Integer.valueOf(map.get("totalContact").toString()));
        detailsVO.setLossContact(Integer.valueOf(map.get("lossContact").toString()));
        detailsVO.setRealAddContact(Integer.valueOf(map.get("realAddContact").toString()));
        detailsVO.setAddContact(Integer.valueOf(map.get("newAddContact").toString()));
        detailsVO.setTime(pattern.format(LocalDateTime.now()));

        return detailsVO;
    }

    @Override
    public List<ContactStaticsTendencyVO> getContactDetailsTendency(ContactDetailsTendencyDTO dto) {
        List<ContactStaticsTendencyVO> vos = new LinkedList<>();
        DateTime date = DateUtil.date();
        DateTime endOfDay = DateUtil.endOfDay(date);
        DateTime parse = DateUtil.parse(dto.getEndTime());

        if (parse.getTime() > endOfDay.getTime()){
            dto.setEndTime(DateUtil.formatDate(endOfDay));
        }
        List<DateTime> hashList = getDetHashList(dto.getStartTime(),dto.getEndTime());
        QueryWrapper<WorkContactEmployeeStatisticsEntity> wrapper = new QueryWrapper<>();
        wrapper.select("create_time,sum(total_contact) as totalContact,sum(new_add_contact) as newAddContact,sum(real_add_contact) as realAddContact,sum(loss_contact) as lossContact");
        wrapper.eq("corp_id",CorpContextHolder.getCorpId());
        wrapper.eq("tenant_id",TenantContextHolder.getTenantId());
        wrapper.ge("create_time",dto.getStartTime() + " 00:00:00");
        wrapper.le("create_time",dto.getEndTime() + " 23:59:59");
        if (dto.getEmployeeId() != null){
            wrapper.in("employee_id", dto.getEmployeeId());
        }
        wrapper.groupBy("create_time");
        List<Map<String, Object>> maps = listMaps(wrapper);

        hashList.forEach(h -> {
            ContactStaticsTendencyVO vo = new ContactStaticsTendencyVO();
            vo.setTime(h.toDateStr());
            vo.setNumber(0);
            vo.setType(dto.getType());
            for (int i = 0; i < maps.size(); i++) {
                DateTime createTime = DateTime.of((Date) maps.get(i).get("create_time"));
                long timeTime = createTime.getTime();
                long time = h.getTime();
                if (timeTime == time) {
                    switch (dto.getType()){
                        case 2:
                            vo.setNumber(Integer.valueOf(maps.get(i).get("newAddContact").toString()));
                            break;
                        case 4:
                            vo.setNumber(Integer.valueOf(maps.get(i).get("realAddContact").toString()));
                            break;
                        case 3:
                            vo.setNumber(Integer.valueOf(maps.get(i).get("lossContact").toString()));
                            break;
                        default:
                            vo.setNumber(Integer.valueOf(maps.get(i).get("totalContact").toString()));
                            break;
                    }
                    maps.remove(i);
                    break;
                }
            }
            vos.add(vo);
        });
        return vos;
    }

    @Override
    public Page<ContactStaticsDetailsVO> getPageList(ContactDetailsTendencyDTO dto, Page page) {
        List<ContactStaticsDetailsVO> vos = new LinkedList<>();
        DateTime date = DateUtil.date();
        DateTime endOfDay = DateUtil.endOfDay(date);
        DateTime parse = DateUtil.parse(dto.getEndTime());

        if (parse.getTime() > endOfDay.getTime()){
            dto.setEndTime(DateUtil.formatDate(endOfDay));
        }
        List<DateTime> hashList = getDetHashList(dto.getStartTime(),dto.getEndTime());
        QueryWrapper<WorkContactEmployeeStatisticsEntity> wrapper = new QueryWrapper<>();
        wrapper.select("create_time,sum(total_contact) as totalContact,sum(new_add_contact) as newAddContact,sum(real_add_contact) as realAddContact,sum(loss_contact) as lossContact");
        wrapper.eq("corp_id",CorpContextHolder.getCorpId());
        wrapper.eq("tenant_id",TenantContextHolder.getTenantId());
        wrapper.ge("create_time",dto.getStartTime() + " 00:00:00");
        wrapper.le("create_time",dto.getEndTime() + " 23:59:59");
        if (dto.getEmployeeId() != null){
            wrapper.in("employee_id", dto.getEmployeeId());
        }
        wrapper.groupBy("create_time");
        List<Map<String, Object>> maps = listMaps(wrapper);

        hashList.forEach(h -> {
            ContactStaticsDetailsVO vo = new ContactStaticsDetailsVO();
            vo.setTime(h.toDateStr());
            vo.setLossContact(0);
            vo.setAddContact(0);
            vo.setRealAddContact(0);
            vo.setTotalContact(0);
            for (int i = 0; i < maps.size(); i++) {
                DateTime createTime = DateTime.of((Date) maps.get(i).get("create_time"));
                long timeTime = createTime.getTime();
                long time = h.getTime();
                if (timeTime == time) {
                    vo.setTotalContact(Integer.valueOf(maps.get(i).get("totalContact").toString()));
                    vo.setAddContact(Integer.valueOf(maps.get(i).get("newAddContact").toString()));
                    vo.setRealAddContact(Integer.valueOf(maps.get(i).get("realAddContact").toString()) );
                    vo.setLossContact(Integer.valueOf(maps.get(i).get("lossContact").toString()) );
                    maps.remove(i);
                    break;
                }
            }
            vos.add(vo);
        });

        if (dto.getSort() == null || dto.getType() == null){
            vos.sort(Comparator.comparing(ContactStaticsDetailsVO::getTime).reversed());
        }else {
            switch (dto.getSort()){
                case 0:
                    switch (dto.getType()){
                        case 2:
                            vos.sort(Comparator.comparing(ContactStaticsDetailsVO::getTotalContact));
                            break;
                        case 3:
                            vos.sort(Comparator.comparing(ContactStaticsDetailsVO::getAddContact));
                            break;
                        case 4:
                            vos.sort(Comparator.comparing(ContactStaticsDetailsVO::getLossContact));
                            break;
                        default:
                            vos.sort(Comparator.comparing(ContactStaticsDetailsVO::getTime));
                            break;
                    }
                    break;
                case 1:
                    switch (dto.getType()){
                        case 2:
                            vos.sort(Comparator.comparing(ContactStaticsDetailsVO::getTotalContact).reversed());
                            break;
                        case 3:
                            vos.sort(Comparator.comparing(ContactStaticsDetailsVO::getAddContact).reversed());
                            break;
                        case 4:
                            vos.sort(Comparator.comparing(ContactStaticsDetailsVO::getLossContact).reversed());
                            break;
                        default:
                            vos.sort(Comparator.comparing(ContactStaticsDetailsVO::getTime).reversed());
                            break;
                    }
                    break;
            }
        }

        Page<ContactStaticsDetailsVO> split = PageUtil.split(vos, (int) page.getCurrent(), (int) page.getSize());

        return split;
    }

    @Override
    public void exitExcel(ContactDetailsTendencyDTO dto, HttpServletResponse response, OutputStream out) {
        List<ContactStaticsDetailsVO> vos = new LinkedList<>();
        DateTime date = DateUtil.date();
        DateTime endOfDay = DateUtil.endOfDay(date);
        DateTime parse = DateUtil.parse(dto.getEndTime());

        if (parse.getTime() > endOfDay.getTime()){
            dto.setEndTime(DateUtil.formatDate(endOfDay));
        }
        List<DateTime> hashList = getDetHashList(dto.getStartTime(),dto.getEndTime());
        QueryWrapper<WorkContactEmployeeStatisticsEntity> wrapper = new QueryWrapper<>();
        wrapper.select("create_time,sum(total_contact) as totalContact,sum(new_add_contact) as newAddContact,sum(real_add_contact) as realAddContact,sum(loss_contact) as lossContact");
        wrapper.eq("corp_id",CorpContextHolder.getCorpId());
        wrapper.eq("tenant_id",TenantContextHolder.getTenantId());
        wrapper.ge("create_time",dto.getStartTime() + " 00:00:00");
        wrapper.le("create_time",dto.getEndTime() + " 23:59:59");
        if (dto.getEmployeeId() != null){
            wrapper.in("employee_id", dto.getEmployeeId());
        }
        wrapper.groupBy("create_time");
        List<Map<String, Object>> maps = listMaps(wrapper);

        hashList.forEach(h -> {
            ContactStaticsDetailsVO vo = new ContactStaticsDetailsVO();
            vo.setTime(h.toDateStr());
            vo.setLossContact(0);
            vo.setAddContact(0);
            vo.setRealAddContact(0);
            vo.setTotalContact(0);
            for (int i = 0; i < maps.size(); i++) {
                DateTime createTime = DateTime.of((Date) maps.get(i).get("create_time"));
                long timeTime = createTime.getTime();
                long time = h.getTime();
                if (timeTime == time) {
                    vo.setTotalContact(Integer.valueOf(maps.get(i).get("totalContact").toString()));
                    vo.setAddContact(Integer.valueOf(maps.get(i).get("newAddContact").toString()));
                    vo.setRealAddContact(Integer.valueOf(maps.get(i).get("realAddContact").toString()) );
                    vo.setLossContact(Integer.valueOf(maps.get(i).get("lossContact").toString()) );
                    maps.remove(i);
                    break;
                }
            }
            vos.add(vo);
        });

        vos.sort(Comparator.comparing(ContactStaticsDetailsVO::getTime).reversed());
        exitExcels(vos,response,out);
    }

    @Override
    public ContactStatisticsDataVO getStatisticsData(Set<Integer> employeeIds) {
        ContactStatisticsDataVO contactStatisticsDataVO = new ContactStatisticsDataVO();
        contactStatisticsDataVO.setContactTotalCount(0);
        contactStatisticsDataVO.setWxContactCount(0);
        contactStatisticsDataVO.setQywxContactCount(0);
        contactStatisticsDataVO.setCrmContactCount(0);
        contactStatisticsDataVO.setNonBankContactCount(0);
        contactStatisticsDataVO.setLossContactCount(0);
        contactStatisticsDataVO.setYesterdayAddedCount(0);
        Integer corpId = CorpContextHolder.getCorpId();
        Integer tenantId = TenantContextHolder.getTenantId();
        List<String> wxUserIds = employeeService.getWxEmpIdListByEmpIdList(new ArrayList<>(employeeIds));

        //客户去重
        List<Integer> contactId = workContactEmployeeService.list(Wrappers.<WorkContactEmployeeEntity>lambdaQuery()
                .eq(WorkContactEmployeeEntity::getCorpId, CorpContextHolder.getCorpId())
                .in(WorkContactEmployeeEntity::getEmployeeId,employeeIds))
                .stream().map(WorkContactEmployeeEntity::getContactId).collect(toList())
                .stream().distinct().collect(toList());
        //客户手机号去重
        List<String> contactMobiles = workContactEmployeeService.list(Wrappers.<WorkContactEmployeeEntity>lambdaQuery()
                .eq(WorkContactEmployeeEntity::getCorpId, CorpContextHolder.getCorpId())
                .in(WorkContactEmployeeEntity::getEmployeeId, employeeIds))
                .stream()
                .map(l -> {
                    l.setRemarkMobiles(JSONArray.parseArray(l.getRemarkMobiles()).isEmpty() ? "" : JSONArray.parseArray(l.getRemarkMobiles()).get(0).toString());
                    return l;
                })
                .filter(l -> !l.getRemarkMobiles().equals(""))
                .map(WorkContactEmployeeEntity::getRemarkMobiles)
                .distinct()
                .collect(Collectors.toList());
        if (contactId.isEmpty()) {
            return contactStatisticsDataVO;
        }
        List<WorkContactEntity> contactList = workContactService.list(new LambdaQueryWrapper<WorkContactEntity>()
                .eq(WorkContactEntity::getCorpId, CorpContextHolder.getCorpId())
                .in(WorkContactEntity::getId, contactId));
        //客户总数量
        Integer contactTotalCount = contactList.size();

        //微信客户数
        Integer wxContactCount = 0;

        //企业微信客户数
        Integer qywxContactCount = 0;
        for (WorkContactEntity contact : contactList) {
            switch (contact.getType()) {
                case 1:
                    wxContactCount += 1;
                    break;
                case 2:
                    qywxContactCount += 1;
                    break;
            }
        }
        //CRM客户数
        Integer crmContactCount = contactMobiles.isEmpty() ? 0 : workCustomInfoService.countInCustMgrNoAndMobile(wxUserIds,contactMobiles);

        //行外客户数 = 客户总数量 - CRM客户数
        Integer nonBankContactCount = contactTotalCount - crmContactCount;

        //流失客户数
        Integer lossContactCount = contactLossService.count(
                Wrappers.<ContactLossEntity>lambdaQuery()
                        .eq(ContactLossEntity::getCorpId, corpId)
                        .eq(ContactLossEntity::getTenantId, tenantId)
                        .in(ContactLossEntity::getEmployeeId, employeeIds));

        //昨日新增客户数
        Integer yesterdayAddedCount = workContactEmployeeService.count(
                Wrappers.<WorkContactEmployeeEntity>lambdaQuery()
                        .eq(WorkContactEmployeeEntity::getCorpId, corpId)
                        .eq(WorkContactEmployeeEntity::getTenantId, tenantId)
                        .in(WorkContactEmployeeEntity::getEmployeeId, employeeIds)
                        .ge(WorkContactEmployeeEntity::getCreateTime,DateUtil.yesterday().toDateStr() + " 00:00:00")
                        .lt(WorkContactEmployeeEntity::getCreateTime,DateUtil.yesterday().toDateStr() + " 23:59:59"));

        //昨日净增客户数
        Integer yesterdayNetGrowthCount = yesterdayAddedCount - lossContactCount < 0 ? 0 : yesterdayAddedCount - lossContactCount;

        contactStatisticsDataVO.setContactTotalCount(contactTotalCount);
        contactStatisticsDataVO.setWxContactCount(wxContactCount);
        contactStatisticsDataVO.setQywxContactCount(qywxContactCount);
        contactStatisticsDataVO.setCrmContactCount(crmContactCount);
        contactStatisticsDataVO.setNonBankContactCount(nonBankContactCount);
        contactStatisticsDataVO.setLossContactCount(lossContactCount);
        contactStatisticsDataVO.setYesterdayAddedCount(yesterdayAddedCount);
        contactStatisticsDataVO.setYesterdayNetGrowthCount(yesterdayNetGrowthCount);
        return contactStatisticsDataVO;
    }

    @Override
    public ContactStatisticsDataVO getStatisticsDatas(Integer deptId, Integer wxDeptId) {
        ContactStatisticsDataVO contactStatisticsDataVO;
        Set<Integer> employeeIds = getEmployees(deptId);
        if (employeeIds == null || employeeIds.size() == 0) {
            contactStatisticsDataVO = new ContactStatisticsDataVO();
            contactStatisticsDataVO.setContactTotalCount(0);
            contactStatisticsDataVO.setWxContactCount(0);
            contactStatisticsDataVO.setQywxContactCount(0);
            contactStatisticsDataVO.setCrmContactCount(0);
            contactStatisticsDataVO.setNonBankContactCount(0);
            contactStatisticsDataVO.setLossContactCount(0);
            contactStatisticsDataVO.setYesterdayAddedCount(0);
        } else {
            contactStatisticsDataVO = getStatisticsData(employeeIds);
        }
        WorkDeptEntity workDeptEntity = deptService.getOne(
                Wrappers.<WorkDeptEntity>lambdaQuery()
                        .eq(WorkDeptEntity::getWxDepartmentId, wxDeptId)
        );
        contactStatisticsDataVO.setCode(String.valueOf(workDeptEntity.getWxDepartmentId()));
        contactStatisticsDataVO.setName(workDeptEntity.getName());
        return contactStatisticsDataVO;
    }

    @Override
    public ContactStatisticsDataVO getStatisticsDatas(String mobile) {
        WorkEmployeeEntity entity = employeeService.getOne(
                Wrappers.<WorkEmployeeEntity>lambdaQuery()
                .select(WorkEmployeeEntity::getId, WorkEmployeeEntity::getWxUserId, WorkEmployeeEntity::getName)
                .eq(WorkEmployeeEntity::getMobile, mobile)
        );
        Set<Integer> employeeIds = new HashSet<>();
        employeeIds.add(entity.getId());
        ContactStatisticsDataVO contactStatisticsDataVO = getStatisticsData(employeeIds);
        contactStatisticsDataVO.setCode(entity.getWxUserId());
        contactStatisticsDataVO.setName(entity.getName());
        return contactStatisticsDataVO;
    }

    @Override
    public List<ContactStaticsTendencyVO> getContactTotalCount(ContactDetailsTendencyDTO dto) {
        Set<Integer> employeeIds = getEmployees(dto.getDeptId());
        List<DateTime> hashList = getDetHashList(dto.getStartTime(), dto.getEndTime());
        Integer corpId = CorpContextHolder.getCorpId();
        List<ContactStaticsTendencyVO> contactStaticsTendencyVOS = new ArrayList<>();

        hashList.forEach(s -> {
            Integer contactTotalCount = 0;
            if (employeeIds != null && employeeIds.size() != 0) {
                contactTotalCount = workContactEmployeeService.list(Wrappers.<WorkContactEmployeeEntity>lambdaQuery()
                        .eq(WorkContactEmployeeEntity::getCorpId, CorpContextHolder.getCorpId())
                        .in(WorkContactEmployeeEntity::getEmployeeId,employeeIds)
                        .le(WorkContactEmployeeEntity::getCreateTime,DateUtil.endOfDay(s)))
                        .stream().map(WorkContactEmployeeEntity::getContactId).collect(toList())
                        .stream().distinct().collect(toList()).size();
            }

            ContactStaticsTendencyVO contactStaticsTendencyVO = new ContactStaticsTendencyVO();
            contactStaticsTendencyVO.setTime(s.toDateStr());
            contactStaticsTendencyVO.setType(1);
            contactStaticsTendencyVO.setNumber(contactTotalCount);
            contactStaticsTendencyVOS.add(contactStaticsTendencyVO);
        });
        return contactStaticsTendencyVOS;
    }

    @Override
    public List<ContactStaticsTendencyVO> getWxContactCount(ContactDetailsTendencyDTO dto) {
        Set<Integer> employeeIds = getEmployees(dto.getDeptId());
        List<DateTime> hashList = getDetHashList(dto.getStartTime(), dto.getEndTime());
        List<ContactStaticsTendencyVO> contactStaticsTendencyVOS = new ArrayList<>();

        hashList.forEach(s -> {
            Integer wxContactCount = 0;
            if (employeeIds != null && employeeIds.size() != 0) {
                wxContactCount = workContactEmployeeService.getContactCountByContactTypeAndEmployeeIds(1,employeeIds,DateUtil.endOfDay(s).toString());
            }

            ContactStaticsTendencyVO contactStaticsTendencyVO = new ContactStaticsTendencyVO();
            contactStaticsTendencyVO.setTime(s.toDateStr());
            contactStaticsTendencyVO.setType(2);
            contactStaticsTendencyVO.setNumber(wxContactCount);
            contactStaticsTendencyVOS.add(contactStaticsTendencyVO);
        });
        return contactStaticsTendencyVOS;
    }

    @Override
    public List<ContactStaticsTendencyVO> getQywxContactCount(ContactDetailsTendencyDTO dto) {
        Set<Integer> employeeIds = getEmployees(dto.getDeptId());
        List<DateTime> hashList = getDetHashList(dto.getStartTime(), dto.getEndTime());
        List<ContactStaticsTendencyVO> contactStaticsTendencyVOS = new ArrayList<>();

        hashList.forEach(s -> {
            Integer qywxContactCount = 0;
            if (employeeIds != null && employeeIds.size() != 0) {
                qywxContactCount = workContactEmployeeService.getContactCountByContactTypeAndEmployeeIds(2,employeeIds,DateUtil.endOfDay(s).toString());
            }

            ContactStaticsTendencyVO contactStaticsTendencyVO = new ContactStaticsTendencyVO();
            contactStaticsTendencyVO.setTime(s.toDateStr());
            contactStaticsTendencyVO.setType(3);
            contactStaticsTendencyVO.setNumber(qywxContactCount);
            contactStaticsTendencyVOS.add(contactStaticsTendencyVO);
        });
        return contactStaticsTendencyVOS;
    }

    @Override
    public List<ContactStaticsTendencyVO> getCrmContactCount(ContactDetailsTendencyDTO dto) {
        Set<Integer> employeeIds = getEmployees(dto.getDeptId());
        List<DateTime> hashList = getDetHashList(dto.getStartTime(), dto.getEndTime());
        List<ContactStaticsTendencyVO> contactStaticsTendencyVOS = new ArrayList<>();
        hashList.forEach(s -> {
            Integer crmContactCount = 0;
            if (employeeIds != null && employeeIds.size() != 0) {
                crmContactCount = this.sumCrmContactInEmployeeIds(CorpContextHolder.getCorpId(),TenantContextHolder.getTenantId(),DateUtil.beginOfDay(s),employeeIds);

            }

            ContactStaticsTendencyVO contactStaticsTendencyVO = new ContactStaticsTendencyVO();
            contactStaticsTendencyVO.setTime(s.toDateStr());
            contactStaticsTendencyVO.setType(4);
            contactStaticsTendencyVO.setNumber(crmContactCount);
            contactStaticsTendencyVOS.add(contactStaticsTendencyVO);
        });
        return contactStaticsTendencyVOS;
    }

    @Override
    public List<ContactStaticsTendencyVO> getNonBankContactCount(ContactDetailsTendencyDTO dto) {
        Set<Integer> employeeIds = getEmployees(dto.getDeptId());
        List<DateTime> hashList = getDetHashList(dto.getStartTime(), dto.getEndTime());
        List<ContactStaticsTendencyVO> contactStaticsTendencyVOS = new ArrayList<>();
        hashList.forEach(s -> {
            Integer nonBankContactCount = 0;
            if (employeeIds != null && employeeIds.size() != 0) {
                nonBankContactCount = this.sumNonBankContactInEmployeeIds(CorpContextHolder.getCorpId(),TenantContextHolder.getTenantId(),DateUtil.beginOfDay(s),employeeIds);
            }

            ContactStaticsTendencyVO contactStaticsTendencyVO = new ContactStaticsTendencyVO();
            contactStaticsTendencyVO.setTime(s.toDateStr());
            contactStaticsTendencyVO.setType(5);
            contactStaticsTendencyVO.setNumber(nonBankContactCount);
            contactStaticsTendencyVOS.add(contactStaticsTendencyVO);
        });
        return contactStaticsTendencyVOS;
    }

    @Override
    public List<ContactStaticsTendencyVO> getLossContactCount(ContactDetailsTendencyDTO dto) {
        Set<Integer> employeeIds = getEmployees(dto.getDeptId());
        Integer corpId = CorpContextHolder.getCorpId();
        Integer tenantId = TenantContextHolder.getTenantId();
        List<DateTime> hashList = getDetHashList(dto.getStartTime(), dto.getEndTime());
        List<ContactStaticsTendencyVO> contactStaticsTendencyVOS = new ArrayList<>();

        hashList.forEach(s -> {
            Integer lossContactCount = 0;
            if (employeeIds != null && employeeIds.size() != 0) {
                lossContactCount = contactLossService.count(
                    Wrappers.<ContactLossEntity>lambdaQuery()
                    .eq(ContactLossEntity::getCorpId, corpId)
                    .eq(ContactLossEntity::getTenantId, tenantId)
                    .in(ContactLossEntity::getEmployeeId, employeeIds)
                            .lt(ContactLossEntity::getCreateTime,s.toDateStr() + " 23:59:59"));
            }

            ContactStaticsTendencyVO contactStaticsTendencyVO = new ContactStaticsTendencyVO();
            contactStaticsTendencyVO.setTime(s.toDateStr());
            contactStaticsTendencyVO.setType(6);
            contactStaticsTendencyVO.setNumber(lossContactCount);
            contactStaticsTendencyVOS.add(contactStaticsTendencyVO);
        });
        return contactStaticsTendencyVOS;
    }

    /**
     * crm客户数求和
     * @param corpId
     * @param tenantId
     * @param dateTime
     * @param employeeIds
     * @return
     */
    @Override
    public Integer sumCrmContactInEmployeeIds(Integer corpId, Integer tenantId, DateTime dateTime, Set<Integer> employeeIds) {
        return baseMapper.sumCrmContactInEmployeeIds(corpId,tenantId,dateTime,employeeIds);
    }

    /**
     * 行外客户数求和
     * @param corpId
     * @param tenantId
     * @param dateTime
     * @param employeeIds
     * @return
     */
    @Override
    public Integer sumNonBankContactInEmployeeIds(Integer corpId, Integer tenantId, DateTime dateTime, Set<Integer> employeeIds) {
        return baseMapper.sumNonBankContactInEmployeeIds(corpId,tenantId,dateTime,employeeIds);
    }

    @Override
    public Long yesterdayContactTotal(Integer employeeId) {
        String yesterday = DateUtil.formatDateTime(DateUtil.beginOfDay(DateUtil.yesterday()));
        WorkContactEmployeeStatisticsEntity entity = this.getOne(
                Wrappers.<WorkContactEmployeeStatisticsEntity>lambdaQuery()
                        .select(WorkContactEmployeeStatisticsEntity::getTotalContact)
                        .eq(WorkContactEmployeeStatisticsEntity::getCreateTime, yesterday)
                        .eq(WorkContactEmployeeStatisticsEntity::getDelFlag, 0)
                        .eq(WorkContactEmployeeStatisticsEntity::getEmployeeId, employeeId), false
        );
        return entity == null ? 0L : entity.getTotalContact();
    }

    private Set<Integer> getEmployees(Integer deptId) {
        R r = remoteDeptService.getDeptsAndUsers(deptId);
        log.info("r:{}",r);
        Map<String, Object> data = (Map) r.getData();
        List<Integer> wxDeptIds = (List<Integer>) data.get("wxDeptIds");
        List<String> userMobiles = (List<String>) data.get("userMobiles");
        return employeeService.getEmployeeIds(wxDeptIds, userMobiles);
    }

    /**
     * 导出Excel
     */
    private void exitExcels(List<ContactStaticsDetailsVO> entityList, HttpServletResponse response, OutputStream out){
        ArrayList<ContactStaticsDetailsVO> rows = CollUtil.newArrayList(entityList);
        if (rows.isEmpty()){
            rows.add(new ContactStaticsDetailsVO());
        }
        // 通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter();



        //自定义标题别名
        writer.addHeaderAlias("time", "日期");
        writer.addHeaderAlias("totalContact", "客户总数");
        writer.addHeaderAlias("realAddContact", "客户净增数");
        writer.addHeaderAlias("addContact", "客户新增数");
        writer.addHeaderAlias("lossContact", "客户流失数");

        Sheet sheet = writer.getSheet();
        setSizeColumn(sheet,4);
        writer.autoSizeColumnAll();

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename=contact.xls");

        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows, true);
        writer.flush(out, true);
        // 关闭writer，释放内存
        writer.close();

        IoUtil.close(out);

    }


    /**
     * 获取两个日期中的所有日期,并转换为表后缀
     *
     * @param begin 格式:yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
     * @param end   格式:yyyy-MM-dd'T'HH:mm:ss.SSS'Z'
     * @return 格式:MM_dd
     */
    public static List<DateTime> getDetHashList(String begin, String end) {
        String btime = begin.substring(0, 10);//yyyy-MM-dd
        String etime = end.substring(0, 10);

        Date bDate = DateUtil.parse(btime, DatePattern.NORM_DATE_PATTERN);//yyyy-MM-dd
        Date eDate = DateUtil.parse(etime, DatePattern.NORM_DATE_PATTERN);
        List<DateTime> dateList = DateUtil.rangeToList(bDate, eDate, DateField.DAY_OF_YEAR);//创建日期范围生成器
        return dateList;
    }


    /**
     * 自适应宽度(中文支持)
     * @param sheet
     * @param size 因为for循环从0开始，size值为 列数-1
     */
    private void setSizeColumn(Sheet sheet, int size) {
        for (int columnNum = 0; columnNum <= size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }

}
